﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting
Imports Microsoft.Reporting.WebForms

Partial Class Reportes_RDLReportePriorizacion
    Inherits System.Web.UI.Page
    Dim storedProcedure As String
    Dim reporteName As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
            hdStoredProcedure.Value = "RDLPriorizacionConsolidada"
            hdReporteName.Value = "rptPriorizacionConsolidada.rdlc"
            cargardatos()
        End If


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
    End Sub
    Sub cargardatos()
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)

            Using da As New SqlDataAdapter

                Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(hdStoredProcedure.Value, cnn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@codProyecto", ddlProyectos.SelectedValue)
                    cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                    cmd.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                    cmd.Parameters.AddWithValue("@depto", ddlDepto.SelectedValue)
                    cmd.Parameters.AddWithValue("@localizacion", ddlLocalizacion.SelectedValue)
                    cmd.Parameters.AddWithValue("@Sexo", ddlSexo.SelectedValue)

                    da.SelectCommand = cmd

                    Using thisDTS As New DataSet
                        da.Fill(thisDTS)
                        Dim datasourse As New ReportDataSource("dtsHogares", thisDTS.Tables(0))
                        ReportViewer1.LocalReport.ReportPath = "Reportes\rdls\" & hdReporteName.Value

                        ReportViewer1.LocalReport.DataSources.Clear()
                        ReportViewer1.LocalReport.DataSources.Add(datasourse)
                        ReportViewer1.LocalReport.Refresh()

                    End Using
                End Using
            End Using
        End Using


    End Sub



    Sub llenarCombos()
        Dim u As New Web_Usuario
        Using h As New Helper
            u = h.GetUsuario(Context.User.Identity.Name)
        End Using
        Using bd As New BdHogaresDataContext
            Dim a = From i In bd.Web_Proyectos
                    Where (i.web_Operadore.idOperador = u.IdOperador) Or IsNothing(u.IdOperador)
                    Select i.idProyecto, i.CodigoProyecto
            ddlProyectos.DataSource = a
            ddlProyectos.DataValueField = "CodigoProyecto"
            ddlProyectos.DataTextField = "CodigoProyecto"
            ddlProyectos.DataBind()

            If IsNothing(u.IdOperador) Then
                ddlProyectos.Items.Insert(0, New ListItem("Todos", "0"))
            End If

            cargarDeptos()
            cargarMunicipios()

            Dim b = From i In bd.Web_Parametros
                    Where (i.Categoria = "visitas")
                    Select i.NombreOpcion, i.ValorOpcion

            ddlVisita.DataSource = b
            ddlVisita.DataValueField = "NombreOpcion"
            ddlVisita.DataTextField = "NombreOpcion"
            ddlVisita.DataBind()
            ddlVisita.Items.Insert(0, New ListItem("Todas", "0"))

        End Using

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruceFiltro", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@campo1", "Localizacion")
                ddlLocalizacion.DataSource = cmd.ExecuteReader
                ddlLocalizacion.DataTextField = "detalle"
                ddlLocalizacion.DataValueField = "valor"
                ddlLocalizacion.DataBind()
                ddlLocalizacion.Items.Insert(0, New ListItem("Todos", "0"))
            End Using

            cnn.Close()

        End Using


        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_OpcionesListar", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@categoria", "Sexo")
                ddlSexo.DataSource = cmd.ExecuteReader
                ddlSexo.DataTextField = "NombreOpcion"
                ddlSexo.DataValueField = "valorOpcion"
                ddlSexo.DataBind()
                ddlSexo.Items.Insert(0, New ListItem("Todos", "0"))
            End Using

            cnn.Close()

        End Using

    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub

#Region "filtros"

    Sub cargarDeptos()
        Using bd As New bdReportesDataContext

            ddlDepto.DataSource = bd.RPT_DepartamentosXProyecto(ddlProyectos.SelectedValue)
            ddlDepto.DataValueField = "Valor"
            ddlDepto.DataTextField = "Detalle"
            ddlDepto.DataBind()
        End Using
    End Sub

    Sub cargarMunicipios()
        Using bd As New bdReportesDataContext

            ddlMunicipio.DataSource = bd.RPT_MunicipiosXProyectoXDepartamento(ddlProyectos.SelectedValue, ddlDepto.SelectedValue)
            ddlMunicipio.DataValueField = "Valor"
            ddlMunicipio.DataTextField = "Detalle"
            ddlMunicipio.DataBind()
        End Using
    End Sub

    Protected Sub ddlProyectos_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlProyectos.SelectedIndexChanged
        cargarDeptos()
        cargarMunicipios()
    End Sub


    Protected Sub ddlDepto_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDepto.SelectedIndexChanged
        cargarMunicipios()
    End Sub
#End Region
End Class
